package com.hnkywl.sts.dao.sys;

import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.hibernate.SQLQuery;
import org.hibernate.tool.hbm2x.StringUtils;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 * User: Administrator
 * Date: 13-2-25
 * Time: 下午3:55
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class WzbmDao extends SimpleHibernateTemplate {
    public Page search(String name,String code,Long id,int level,int pageNo,int pageSize) {
        StringBuilder hql = new StringBuilder();
        StringBuilder hqlCount = new StringBuilder();
        List<Object> param = new ArrayList<Object>();
        List<Object> parameter = new ArrayList<Object>();
        StringBuilder condition = new StringBuilder();
        Page page = new Page(pageNo, pageSize, 0);
        //==============条件拼接===============

        if (name != null && !name.isEmpty()) {
            condition.append(" name like ? ");
            param.add("%" + name.trim() + "%");
        }
        if (code != null && !code.isEmpty()) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" code like ? ");
            param.add("%" + code.trim() + "%");
        }
        if (level < 1) {
            hql.append("select id,name,code,1 as l from T_SYS_YJWZBM ");
            hqlCount.append("select count(1) count from T_SYS_YJWZBM ");
            if (param.size() != 0) {
                hql.append(" where " + condition);
                hqlCount.append(" where " + condition);
                parameter.addAll(param);
            }
        }
        if (level < 2) {
            if (hql.length() != 0) {
                hql.append(" union ");
                hqlCount.append(" union ");
            }
            hql.append("select id,name,code,2 as l from T_SYS_RJWZBM ");
            hqlCount.append("select count(1) count from T_SYS_RJWZBM ");
            if (param.size() != 0) {
                hql.append(" where " + condition);
                hqlCount.append(" where " + condition);
                parameter.addAll(param);
            }
        }
        if (level < 3) {
            if (hql.length() != 0) {
                hql.append(" union ");
                hqlCount.append(" union ");
            }
            hql.append("select id,name,code,3 as l from T_SYS_PM ");
            hqlCount.append("select count(1) count from T_SYS_PM ");
            if (param.size() != 0) {
                hql.append(" where " + condition);
                hqlCount.append(" where " + condition);
                parameter.addAll(param);
            }
        }
        if (level < 4) {
            if (hql.length() != 0) {
                hql.append(" union ");
                hqlCount.append(" union ");
            }
            hql.append("select id,name,code,4 as l from T_SYS_GG ");
            hqlCount.append("select count(1) count from T_SYS_GG ");
            if (param.size() != 0) {
                hql.append(" where " + condition);
                hqlCount.append(" where " + condition);
                parameter.addAll(param);
            }
        }
        //==============记录数量===============
        String countSql = "select sum(count) from (" + hqlCount + " )";
        BigDecimal c;
        if (param.size() != 0) {
            c = (BigDecimal) findBySQL(countSql, parameter.toArray()).get(0);
            page.setTotalCount(c.intValue());
        } else {
            c = (BigDecimal) findBySQL(countSql).get(0);
            page.setTotalCount(c.intValue());
        }
        if (c.intValue() == 0)
            return page;
        //==============查询结果===============
        if (param.size() != 0)
            return findBySql(page, hql.toString(), parameter.toArray());

        return findBySql(page, hql.toString());
    }

    public Page findBySql(Page page,String sql,Object... values) {
        SQLQuery queryObject = getSession().createSQLQuery(sql);
        if (values != null) {
            for (int i = 0; i < values.length; ++i) {
                queryObject.setParameter(i, values[i]);
            }
        }
        if (page.isFirstSetted()) {
            queryObject.setFirstResult(page.getFirst());
        }
        if (page.isPageSizeSetted()) {
            queryObject.setMaxResults(page.getPageSize());
        }
        page.setResult(queryObject.list());
        return page;
    }

    public Object[] findByCode(String code,String bm){
        String sql = "select wzbm.id as \"id\", wzbm.code as \"code\" " +
                "from t_sys_@bm wzbm left join t_sys_bmb bmb on (wzbm.code = bmb.bmz) " +
                "where bmb.zt = '1' and bmb.bmylz2 = :code";
        sql = sql.replaceAll("@bm",bm);
        SQLQuery sqlQuery = getSession().createSQLQuery(sql);
        sqlQuery.setString("code", code);
        Object[] obj = (Object[]) sqlQuery.uniqueResult();
        return obj;
    }

    /**
     * 所有物资编码树的查询
     * @param sjwzbmId 上级编码Id
     * @param bm 表名
     * @param sjwzbmbm 上级物资编码名称
     * @return
     */
    public List<Map> findTreeJson(Long sjwzbmId,String bm,String sjwzbmbm){
        String select = "select wzbm.id as \"wzbmId\",wzbm.name as \"name\",wzbm.code as \"code\",bm.bmylz2 as \"bgCode\""
                + (bm.equalsIgnoreCase("pm")?",wzbm.gysmc as \"gysmc\"":" ");
        String from = "from t_sys_@bm wzbm " +
                "left join t_sys_bmb bm on (wzbm.code = bm.bmz) " +
                "left join t_sys_bmlx bmlx on (bm.bmlxid = bmlx.id)";
        StringBuilder where = new StringBuilder();
        where.append("bm.bmz is not null and lower(bmlx.lxzjm) = '@bm' and bm.zt = '1'");
        if(sjwzbmId != null){
            where .append("and wzbm.@sjwzbmbm = :sjwzbmId");
        }
        String sql = select + from + " where " + where + " order by wzbm.code asc";
        sql = sql.replaceAll("@bm",bm).replaceAll("@sjwzbmbm",sjwzbmbm);
        SQLQuery sqlQuery = getSession().createSQLQuery(sql);
        if(sjwzbmId != null){
            sqlQuery.setLong("sjwzbmId",sjwzbmId);
        }
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return sqlQuery.list();
    }

    public List<Map> findTreeJsonByCodeid(String codeId){
        String sql = "select t.* from (select distinct '('||y.code||')'||y.name \"text\",y.id||':1' \"id\",'0:0' \"parentId\", 'closed' \"state\",y.code " +
                "  from t_sys_pm p, t_sys_rjwzbm r, t_sys_yjwzbm y, t_sys_pm_cy c,t_sys_bmb b " +
                " where p.id = c.pm_id and p.rjwzbm_id = r.id and r.yjwzbm_id = y.id " +
                "   and p.code = b.bmz and b.bmlxid = 3 and b.zt = '1' " +
                "   and p.deleted = 0 and r.deleted = 0 and y.deleted = 0 and c.codeid = :codeid " +
                "union all " +
                "select distinct '('||r.code||')'||r.name \"text\",r.id||':2' \"id\",r.yjwzbm_id||':1' \"parentId\",'closed' \"state\",r.code " +
                "  from t_sys_pm p, t_sys_rjwzbm r, t_sys_pm_cy c,t_sys_bmb b " +
                " where p.id = c.pm_id and p.rjwzbm_id = r.id " +
                "   and p.code = b.bmz and b.bmlxid = 3 and b.zt = '1' " +
                "   and r.deleted = 0 and p.deleted = 0 and c.codeid = :codeid " +
                "union all " +
                "select '('||p.code||')'||p.name \"text\",p.id||':3' \"id\",p.rjwzbm_id||':2' \"parentId\",'open' \"state\",p.code " +
                "  from t_sys_pm p, t_sys_pm_cy c,t_sys_bmb b " +
                " where p.id = c.pm_id and p.code = b.bmz and b.bmlxid = 3 and b.zt = '1' " +
                "   and p.deleted = 0 and c.codeid = :codeid ) t order by t.code";
        SQLQuery sqlQuery = getSession().createSQLQuery(sql);
        if(codeId != null){
            sqlQuery.setString("codeid",codeId);
        }
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return sqlQuery.list();
    }
}
